package com.soilflow.web.utils;


import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    /**
     * 该方法可根据指定字段对List<Map>中的数据去重，存在重复的，保存第一组Map
     * @param originMapList 源Map数组
     * @param keys 依据的去重字段
     * @return
     */
    public static List deleteDuplicatedMapFromListByKeys(List<JSONObject> originMapList, List keys){
        if(CollectionUtils.isEmpty(originMapList)) return null;
        Map tempMap = new HashMap();
        for (Map originMap : originMapList) {
            String objHashCode = "";
            for (Object key : keys) {
                String value = originMap.get(key) != null ? originMap.get(key).toString()  : "";
                objHashCode += value.hashCode();
            }
            tempMap.put(objHashCode,originMap);
        }
        List valueList = new ArrayList<>(tempMap.values());
        return valueList;
    }


    /**
     * MultipartFile 转换为 File 文件
     *
     * @param multipartFile
     * @return
     */
    public final static File transferToFile(MultipartFile multipartFile) {
        //选择用缓冲区来实现这个转换即使用java 创建的临时文件 使用 MultipartFile.transferto()方法 。
        File file = null;
        try {
            String originalFilename = multipartFile.getOriginalFilename();
            String[] filename = originalFilename.split("\\.");
            file = File.createTempFile(filename[0], filename[1]);    //注意下面的 特别注意！！！
            multipartFile.transferTo(file);
            file.deleteOnExit();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

    /**
     * File 装换为 MultipartFile
     * @param file
     * @return
     * @throws IOException
     */
    public static MultipartFile fileToMultipartFile(File file){
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem item=factory.createItem(file.getName(),"text/plain",true,file.getName());
        int bytesRead = 0;
        byte[] buffer = new byte[8192];
        try {
            FileInputStream fis = new FileInputStream(file);
            OutputStream os = item.getOutputStream();
            while ((bytesRead = fis.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        MultipartFile multipartFile = new CommonsMultipartFile(item);
        return  multipartFile;

    }


    /**
     * 读取excel数据 支持xls格式
     * @param sheetIndex    sheet页下标：从0开始
     * @param startReadLine 开始读取的行:从0开始
     * @param tailLine      去除最后读取的行
     */
    public static List<List<JSONObject>> readMergeExcel(File file, int sheetIndex, int startReadLine, int tailLine) {
        List<List<JSONObject>> results = new ArrayList<>();
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(file);
            Sheet sheet = wb.getSheetAt(sheetIndex);
            Row row = null;
            for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                List<JSONObject> result = new ArrayList<>();
                for (Cell c : row) {
                    JSONObject values = new JSONObject();
                    boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                    values.put("isMerge",isMerge);
                    // 判断是否具有合并单元格
                    if (isMerge) {
                        JSONObject rs = getMergedRegionJsonValue(sheet, row.getRowNum(), c.getColumnIndex());
                        values.putAll(rs);
                    } else {
                        values.put("cellValue",getCellValue(c));
                    }
                    result.add(values);
                }
                results.add(result);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return results;
    }

    /**
     * 获取合并单元格的值
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    public static JSONObject getMergedRegionJsonValue(Sheet sheet, int row, int column) {
        JSONObject values = new JSONObject();
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    values.put("cellValue",getCellValue(fCell));
                    values.put("firstColumn",firstColumn);
                    values.put("lastColumn",lastColumn);
                    values.put("firstRow",firstRow);
                    values.put("lastRow",lastRow);
                    return values;
                }
            }
        }
        return values;
    }

    /**
     * 判断合并了行
     */
    private boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param row    行下标
     * @param column 列下标
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断sheet页中是否含有合并单元格
     */
    private boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     */
    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 获取单元格的值
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {return "";}
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        }
        if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        }
        if (cell.getCellType() ==  CellType.FORMULA) {
            return cell.getCellFormula();
        }
        if (cell.getCellType() ==  CellType.NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
//            return String.valueOf(cell.getNumericCellValue());
            return df.format(cell.getNumericCellValue());
        }
        return "";
    }
}
